package dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;

import common.Constants;
import dao.EmpDao;
import entity.Emp;
import util.DBPoolUtils;

public class EmpDaoImpl implements EmpDao {

	@Override
	public boolean insert(Emp emp) {
		// try() 会自动关闭close
		try(Connection c = DBPoolUtils.getConnection()){
			String sql = "insert into emp values(?,?,?,?,?,?,?,?)";
			PreparedStatement ps = c.prepareStatement(sql);
			//设置参数
			ps.setObject(1,emp.getEmpno());
			ps.setObject(2,emp.getEname());
			ps.setObject(3,emp.getJob());
			ps.setObject(4,emp.getMgr());
			ps.setObject(5,emp.getHiredate());
			ps.setObject(6,emp.getSal());
			ps.setObject(7,emp.getComm());
			ps.setObject(8,emp.getDeptno());
			// 执行
			int row = ps.executeUpdate();
			return row == 1 ? true : false;
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		}
	}

	@Override
	public boolean update(Emp emp) {
		try(Connection c = DBPoolUtils.getConnection()){
			String sql = "update emp set ename=?,job=?,mgr=?,sal=? where empno=?";
			PreparedStatement ps = c.prepareStatement(sql);
			ps.setObject(1, emp.getEname());
			ps.setObject(2, emp.getJob());
			ps.setObject(3, emp.getMgr());
			ps.setObject(4, emp.getSal());
			ps.setObject(5, emp.getEmpno());
			
			int row = ps.executeUpdate();
			return 1 == row ? true : false;
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return false;
	}

	@Override
	public boolean delete(int empno) {
		try (Connection c = DBPoolUtils.getConnection()){
			String sql = "delete from emp where empno = ?";
			PreparedStatement ps = c.prepareStatement(sql);
			ps.setInt(1, empno);
			int row = ps.executeUpdate();
			return 1 == row ? true : false;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return false;
	}

	@Override
	public List<Emp> selectAll() {
		List<Emp> emps = new ArrayList<>();
		
		try(Connection c = DBPoolUtils.getConnection()){
			String sql = "select empno,ename,job,mgr,hiredate,sal,comm,deptno  "
					+ " from emp";
			PreparedStatement ps = c.prepareStatement(sql);
			ResultSet rs = ps.executeQuery();
			// 遍历结果集，并封装到对象，把对象添加到集合
			while (rs.next()) {
				Emp emp = new Emp();
				emp.setEmpno(rs.getInt(1));
				emp.setEname(rs.getString(2));
				emp.setJob(rs.getString(3));
				emp.setMgr(rs.getInt(4));
				emp.setHiredate(rs.getDate(5));
				emp.setSal(rs.getInt(6));
				emp.setComm(rs.getInt(7));
				emp.setDeptno(rs.getInt(8));
				
				emps.add(emp);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		// 如果是空集合返回一个空的集合，否则返回集合本身
		return emps.isEmpty() ? Collections.emptyList() : emps;
	}

	@Override
	public List<Emp> selectByPage(int page) {
		List<Emp> emps = new ArrayList<>();
		
		try(Connection c = DBPoolUtils.getConnection()){
			String sql = "select empno,ename,job,mgr,hiredate,sal,comm,deptno  "
					+ " from emp limit ?,?";
			PreparedStatement ps = c.prepareStatement(sql);
			ps.setInt(1, (page-1) * Constants.PAGE_SIZE);
			ps.setInt(2, Constants.PAGE_SIZE);
			ResultSet rs = ps.executeQuery();
			// 遍历结果集，并封装到对象，把对象添加到集合
			while (rs.next()) {
				Emp emp = new Emp();
				emp.setEmpno(rs.getInt(1));
				emp.setEname(rs.getString(2));
				emp.setJob(rs.getString(3));
				emp.setMgr(rs.getInt(4));
				emp.setHiredate(rs.getDate(5));
				emp.setSal(rs.getInt(6));
				emp.setComm(rs.getInt(7));
				emp.setDeptno(rs.getInt(8));
				
				emps.add(emp);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		// 如果是空集合返回一个空的集合，否则返回集合本身
		return emps.isEmpty() ? Collections.emptyList() : emps;
	}

	@Override
	public List<Emp> selectByName(String ename) {
		List<Emp> emps = new ArrayList<>();
		
		try(Connection c = DBPoolUtils.getConnection()){
			String sql = "select empno,ename,job,mgr,hiredate,sal,comm,deptno  "
					+ " from emp where instr(ename,?)>0";
			PreparedStatement ps = c.prepareStatement(sql);
			
			ps.setString(1, ename);
			
			ResultSet rs = ps.executeQuery();
			// 遍历结果集，并封装到对象，把对象添加到集合
			while (rs.next()) {
				Emp emp = new Emp();
				emp.setEmpno(rs.getInt(1));
				emp.setEname(rs.getString(2));
				emp.setJob(rs.getString(3));
				emp.setMgr(rs.getInt(4));
				emp.setHiredate(rs.getDate(5));
				emp.setSal(rs.getInt(6));
				emp.setComm(rs.getInt(7));
				emp.setDeptno(rs.getInt(8));
				
				emps.add(emp);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		// 如果是空集合返回一个空的集合，否则返回集合本身
		return emps.isEmpty() ? Collections.emptyList() : emps;
	}

	@Override
	public Emp selectById(int empno) {
		try(Connection c = DBPoolUtils.getConnection()){
			String sql = "select empno,ename,job,mgr,hiredate,sal,comm,deptno  "
					+ " from emp where empno=?";
			PreparedStatement ps = c.prepareStatement(sql);
			
			ps.setInt(1, empno);
			
			ResultSet rs = ps.executeQuery();
			// 遍历结果集，并封装到对象，把对象添加到集合
			while (rs.next()) {
				Emp emp = new Emp();
				emp.setEmpno(rs.getInt(1));
				emp.setEname(rs.getString(2));
				emp.setJob(rs.getString(3));
				emp.setMgr(rs.getInt(4));
				emp.setHiredate(rs.getDate(5));
				emp.setSal(rs.getInt(6));
				emp.setComm(rs.getInt(7));
				emp.setDeptno(rs.getInt(8));
				return emp;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}

}
